cenix
boombassrecords
SQL for beginners
skill level: easy
:: Welcome
Welcome to my tutorial: SQL for beginners. SQL (speak: 'Es Que Ell') stands for Structured Query Language.
In this tutorial we are going to create a database which stores your release information. If you want to see how it's implemented into a PHP driven page, see Sweeper's PHP tutorial. If you don't know what PHP is or how to code it, check my PHP for beginners tutorial found in this issue of Hugi.
:: CREATE a Database
To retrieve your release information, we must have something where all this information is stored. Therefore we choose to make a database. Into this database we place information such as: Name(Title), Creator, Type, Release Date, Downloadable from, etc.
As we create such a database we get some sort of table which looks something like this. (This may differ from your chosen items, but the idea is the same.)
+---------------+---------------+-------+---------------+-----------------------+
| Name | Creator | Type | Release Date | URL |
+---------------+---------------+-------+---------------+-----------------------+
| | | | | |
| | | | | |
+---------------+---------------+-------+---------------+-----------------------+
To create this table SQL has a command CREATE TABLE. Syntax:
CREATE TABLE <tablename> (
<columnname> <columntype> [(<length>)] [NOT NULL],
<columnname> <columntype> [(<length>)] [NOT NULL]
);
Each column (in the table) is separated by a comma.
For the tablename you may choose a name which you like, for example: 'releases'.
For the columnname you also may choose a name, for example: 'name' or 'style'.
For the columntype you can choose one of these:
| CHAR | characters |
| NUMBER | numbers / non-characters |
| DATE or DATETIME | date or date & time |
| LONG | long characters |
The length is optional, it's only required with CHAR.
NOT NULL states that that particular value has no value.
By this time, we have determined what our database is going to look like.
+---------------+---------------+---------------+-------------------------------+
| Name | Type | Length | Additions |
+---------------+---------------+---------------+-------------------------------+
| Name | CHAR | 30 | NOT NULL |
| Creator | CHAR | 30 | NOT NULL |
| Type | CHAR | 10 | |
| Release_Date | DATE | | |
| URL | CHAR | 50 | |
+---------------+---------------+---------------+-------------------------------+
In this database there is no Primary Key (Value that may exist only 1 time!) defined. If we would create a member list, a primary key must exist, because there could be 2 or more members with the same name, and which one is the correct?? See the example below to see what I mean: (this example doesn't fit in our tutorial actually)
+-------+---------------+---------------+---------------+-----------------------+
| ID(*) | Last Name | Name | Phone | Address |
+-------+---------------+---------------+---------------+-----------------------+
| 00001 | King | Jack | 01-23456789 | Houston Av. 34 |
| 00002 | King | Tom | 01-98765432 | Middle E. street 99 |
| 00003 | Wood | Gary | 09-87654321 | Woodstock 354 |
| 00004 | Wood | Gary | 05-12346789 | Houston Av. 86 |
+-------+---------------+---------------+---------------+-----------------------+
( * = Primary Key)
See that there are Two Kings? Could be a problem. In this case NOT, because there is only 1 Jack King and 1 Tom King. But.. we also have 2 Woods! And this time 2 Gary Woods! Which one is the correct if you want to raise his salary?? Use his ID instead! And if you want to raise Gary's salary, raise it to 00003 instead of to Gary Wood because two Garys are happy -> you're not.
To define a primary key, please in the CREATE TABLE command:
Primary Key ( [columname] )
So in OUR (not the salary example) database this would be
Primary Key (Name)
Now would be a good time to actually create our database.
CREATE TABLE releases (
Name CHAR(30) NOT NULL,
Creator CHAR(30) NOT NULL,
Type CHAR(10),
Release_Date DATE,
URL CHAR(50),
Primary Key(Name)
);
:: INSERT into a database
By this time we have created our database, but the database has no information to show! To insert data into your database use the INSERT command. Syntax:
INSERT INTO <tablename> [(columnname, ...)] VALUES (value, ...);
In our database we add the release "Storm" created by "Cenix", it is an "XM". The release Date is set at "2001-01-14" and the URL (for download is) "http://download_storm". Thus we get:
INSERT INTO releases (Name, Creator, Type, Release_Date, URL)
VALUES ('Storm','Cenix','XM','2001-01-14','http://download_storm');
Now the database looks like this:
+---------------+---------------+-------+---------------+-----------------------+
| Name | Creator | Type | Release Date | URL |
+---------------+---------------+-------+---------------+-----------------------+
| Storm | Cenix | XM | 2001-01-14 | http://download_storm |
| | | | | |
+---------------+---------------+-------+---------------+-----------------------+
:: SELECT from a database
Now we have the information entered into the database. But we would see what's inside the database, wouldn't we? Herefore the SELECT command is the right one to use. Syntax:
SELECT <data> FROM <tablename> [WHERE <columnname> = '<value>']
To select all data from the database use
SELECT * FROM releases
Or to select all data from the database which contains the name "Cenix" use
SELECT * FROM releases WHERE Creator = 'Cenix'
We also can select just a few columns from the database, therefore use
SELECT Name,Creator,Type FROM releases
:: WHERE additives
As we saw before we could use the WHERE command with the SELECT command. We have seen that we can select values which equals (=) to a specified value. But we can do more! We can also use the < and/or > to have other values. Some examples are
SELECT * FROM releases WHERE Release_Date > 2000-01-01
SELECT * FROM releases WHERE Release_Data < 2001-01-14
Please be aware that when you use < or > at CHAR columns you might get different values than what you expected because ASCII characters are CASE SENSITIVE, this means that character 'a' has a lower value than 'A'.
The operators are:
| > | greater than |
| < | smaller than |
| != | value is NOT |
| >= | greater than or equals |
| <= | smaller than or equals |
| <> | equals NOT (is not the same as) |
And it still doesn't end here. We can use AND and OR to create multiple conditions. Example:
SELECT * FROM releases WHERE Creator = 'Cenix' AND Type = 'XM'
:: UPDATE a database
With this command we can mutate a certain value inside a database. Syntax:
UPDATE <tablename> SET <columname> = '<value>'
WHERE <columnname> = '<value>'
If we, for example, have written "Strom" instead of "Storm" you can mutate the information. Then we could write
UPDATE releases SET Name = 'Storm' WHERE Name = 'Strom'
:: Automation
You see that SQL is not that difficult. You can use this to automate your site. Just fill in a form and submit it to the SQL database. If you want to achieve this, please read the article written by Sweeper (found in this issue of Hugi).
:: END
And here I like to end this tutorial about SQL for beginners. I hope you have learned a lot of this tutorial.
For questions about (beginning) SQL, unclear issues, and more:
cenix
boombassrecords.
e-mail: b_clephas@hotmail.com